package com.model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.javabean.MeetingRoom;

public class MeetingService {

	// 订阅会议室
	public boolean ReserveRoom(String ReserveDate, int RoomID, int OccupyTime,
			int AttenderNumber, String ReserverName, String ConnectorName,
			String ConnectMeans, String Description,String Department,int UserID ) {
		boolean isSuccess = false;
		String sql="insert into meetingroom( ReserveDate,RoomID,OccupyTime,AttenderNumber,ReserverName,ConnectorName,ConnectMeans,Description,Department,UserId)" +
				"values('"+ReserveDate+"',"+RoomID +","+ OccupyTime+","+AttenderNumber +"," +
						"'"+ReserverName +"','"+ ConnectorName+"','"+ ConnectMeans+"','"+ Description+"','"+Department +"',"+UserID+");";
		try {
			Connection con = DataBase.getConnection();
			/*PreparedStatement pstmtEdit = con
					.prepareStatement("insert into MEETINGROOM( ReserveDate,RoomID,OccupyTime,AttenderNumber,ReserverName,ConnectorName,ConnectMeans,Description,Department) values(?,?,?,?,?,?,?,?,?);");
			pstmtEdit.setString(1, ReserveDate);
			pstmtEdit.setInt(2, RoomID);
			pstmtEdit.setInt(3, OccupyTime);
			pstmtEdit.setInt(4, AttenderNumber);
			pstmtEdit.setString(5, ReserverName);
			pstmtEdit.setString(6, ConnectorName);
			pstmtEdit.setString(7, ConnectMeans);
			pstmtEdit.setString(8, Description);
			pstmtEdit.setString(9, Department);
			isSuccess = pstmtEdit.execute();*/
			// close work
			Statement stat=con.createStatement();
            int result=stat.executeUpdate(sql);
			if(result==1){
				isSuccess=true;
			}
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		return isSuccess;
	}

	// 取消会议室
	public boolean CancelRoom(int ReserveID) {
		boolean isSuccess = false;
		String sql = "DELETE FROM meetingroom WHERE ReserveID='" + ReserveID + "'";
		try {
			Connection con = DataBase.getConnection();
			Statement stat = con.createStatement();
			int result = stat.executeUpdate(sql);
			if (result == 1) {
				isSuccess = true;
			}
			;
			// close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		return isSuccess;
	}
	// 更改会议状态
		public boolean changeRoomState(int state,int ReserveID) {
			boolean isSuccess = false;
			String sql = "UPDATE campus_information.meetingroom SET State="+state +" WHERE ReserveID="+ReserveID+"";
			try {
				Connection con = DataBase.getConnection();
				Statement stat = con.createStatement();
				int result = stat.executeUpdate(sql);
				if (result == 1) {
					isSuccess = true;
				}
				;
				// close work
				con.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
			return isSuccess;
		}
		// 根据ID删除预约
				public boolean deleteRoom(int ReserveID) {
					boolean isSuccess = false;
					String sql = "DELETE FROM campus_information.meetingroom WHERE ReserveID="+ReserveID+"";
					try {
						Connection con = DataBase.getConnection();
						Statement stat = con.createStatement();
						int result = stat.executeUpdate(sql);
						if (result == 1) {
							isSuccess = true;
						}
						;
						// close work
						con.close();
					} catch (SQLException e2) {
						e2.printStackTrace();
					}
					return isSuccess;
				}
	//查询某天会议信息
	public ArrayList<MeetingRoom> findMeetingRoom(String reserveDate){
		ArrayList<MeetingRoom> rooms=new ArrayList<MeetingRoom>();
		String sql="SELECT * FROM campus_information.meetingroom WHERE ReserveDate='"+reserveDate+"' ";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	/*表的的参数
    			String ReserveDate, int RoomID, int OccupyTime,
    			int AttenderNumber, String ReserverName, String ConnectorName,
    			String ConnectMeans, String Description*/
            	MeetingRoom room=new MeetingRoom();
               	room.setReserveDate(rs.getString("ReserveDate"));
            	room.setReserveID(rs.getInt("ReserveID"));
            	room.setOccupyTime(rs.getInt("OccupyTime"));
            	room.setAttenderNumber(rs.getInt("AttenderNumber"));
            	room.setState(rs.getInt("State"));
            	room.setDepartment(rs.getString("Department"));
            	room.setRoomID(rs.getInt("RoomID"));
            	room.setDescription(rs.getString("Description"));
            	room.setReserverName(rs.getString("ReserverName"));
            	room.setConnectorName(rs.getString("ConnectorName"));
            	room.setConnectMeans(rs.getString("ConnectMeans"));
            	room.setUserID(rs.getInt("UserId"));
            	rooms.add(room);
            	//System.out.println("State:"+room.getState());
            }
			//close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
	}
		return rooms;
}
	//查询时，用时间返回当前房间
	public ArrayList<MeetingRoom> findMeetingRoomByDate(String reserveDate){
		ArrayList<MeetingRoom> rooms=new ArrayList<MeetingRoom>();
		String sql="SELECT * FROM campus_information.MeetingRoom WHERE ReserveDate LIKE '%"+reserveDate+"%' ORDER BY ReserveID DESC";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	/*表的的参数
    			String ReserveDate, int RoomID, int OccupyTime,
    			int AttenderNumber, String ReserverName, String ConnectorName,
    			String ConnectMeans, String Description*/
            	MeetingRoom room=new MeetingRoom();
               	room.setReserveDate(rs.getString("ReserveDate"));
            	room.setReserveID(rs.getInt("ReserveID"));
            	room.setOccupyTime(rs.getInt("OccupyTime"));
            	room.setAttenderNumber(rs.getInt("AttenderNumber"));
            	room.setState(rs.getInt("State"));
            	room.setDepartment(rs.getString("Department"));
            	room.setRoomID(rs.getInt("RoomID"));
            	room.setDescription(rs.getString("Description"));
            	room.setReserverName(rs.getString("ReserverName"));
            	room.setConnectorName(rs.getString("ConnectorName"));
            	room.setConnectMeans(rs.getString("ConnectMeans"));
            	room.setUserID(rs.getInt("UserId"));
            	rooms.add(room);
            }
			//close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
	}
		return rooms;
}
	//查询时用房间号返回时间 
		public ArrayList<MeetingRoom> findMeetingRoomByID(int RoomID){
			ArrayList<MeetingRoom> rooms=new ArrayList<MeetingRoom>();
			String sql="SELECT * FROM campus_information.meetingroom WHERE RoomID = '"+RoomID+"' ORDER BY ReserveID DESC";
			try {
				Connection con = DataBase.getConnection();
	            Statement stat=con.createStatement();
	            ResultSet rs=stat.executeQuery(sql);
	            while(rs.next())
	            {
	            	/*表的的参数
	    			String ReserveDate, int RoomID, int OccupyTime,
	    			int AttenderNumber, String ReserverName, String ConnectorName,
	    			String ConnectMeans, String Description*/
	            	MeetingRoom room=new MeetingRoom();
	               	room.setReserveDate(rs.getString("ReserveDate"));
	            	room.setReserveID(rs.getInt("ReserveID"));
	            	room.setOccupyTime(rs.getInt("OccupyTime"));
	            	room.setAttenderNumber(rs.getInt("AttenderNumber"));
	            	room.setState(rs.getInt("State"));
	            	room.setDepartment(rs.getString("Department"));
	            	room.setRoomID(rs.getInt("RoomID"));
	            	room.setDescription(rs.getString("Description"));
	            	room.setReserverName(rs.getString("ReserverName"));
	            	room.setConnectorName(rs.getString("ConnectorName"));
	            	room.setConnectMeans(rs.getString("ConnectMeans"));
	            	room.setUserID(rs.getInt("UserId"));
	            	rooms.add(room);
	            }
				//close work
				con.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
		}
			return rooms;
	}
	//判断当天是否预约满了   1   2013-05   ---> 2013-05-1
	public int isAvailable(String day,String presenttime) {
		String date=presenttime+"-"+day;
		String sql = "SELECT COUNT(*) FROM campus_information.meetingroom WHERE ReserveDate='"+date+"' AND (state=3 OR state=2)";
		int num=0;
		try {
			Connection con = DataBase.getConnection();
			Statement stat = con.createStatement();
			ResultSet rs=stat.executeQuery(sql);
			//int[] room={0,0,0,0};
			
			while(rs.next()){
				/*
			  int k=rs.getInt("OccupyTime");
			   switch (k){
			   case 1:
				   room[0]++;
				   break;
			   case 2:
				   room[1]++;
				   break;
			   case 3:
				   room[2]++;
				   break;
			   case 4:
				   room[3]++;
				   break;
			   }
			}
			for(int i=0;i<4;i++){
				if(room[i]==4){
					room[i]=1;
				}else{
					room[i]=0;
				}
			}
			result = (room[0])|(room[1]<<1)|(room[2]<<2)|(room[3]<<3);*/
			// close work
			num = rs.getInt(1);
			}
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		return num;
		
	}
	//判断特定会议室 当日 特定会议室 时间段能否预约
	public boolean checkRoom(String ReserveDate, int RoomID, int OccupyTime) {
		boolean isSuccess = false;
		String sql="SELECT * FROM campus_information.meetingroom WHERE ReserveDate='"+ReserveDate+"' AND RoomID='"+RoomID+"' AND OccupyTime='"+OccupyTime+"'";
		try {
			Connection con = DataBase.getConnection();
			Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
			if(!rs.next()){
				isSuccess=true;
			}
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		return isSuccess;
	}
  //会议室分页 根据时间查询
	public ArrayList<MeetingRoom> findRooms(String time,int pageCountNow){
		ArrayList<MeetingRoom> meetingrooms=new ArrayList<MeetingRoom>();
		int pageBefore=(pageCountNow-1)*10;
		String sql="SELECT * FROM campus_information.meetingroom WHERE ReserveDate LIKE '%"+time+"%' ORDER BY ReserveID DESC LIMIT "+pageBefore+",10";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	/*   ReserveID   BIGINT(20) NOT NULL AUTO_INCREMENT ,  
   ReserveDate   varchar(40)  NOT NULL,
   RoomID       int  NOT NULL,
   OccupyTime   int  not null,
   AttenderNumber int not null, 
   ReserverName  varchar(30)  NOT NULL,
   ConnectorName varchar(30)  NOT NULL,
   ConnectMeans  varchar(30)  NOT NULL,
   Description   varchar(300)  default '无',
   Department    varchar(30)  not null,
   State    int default 0,*/
            	MeetingRoom meetroom=new MeetingRoom();
            	meetroom.setReserveDate(rs.getString("ReserveDate"));
            	meetroom.setReserveID(rs.getInt("ReserveID"));
            	meetroom.setOccupyTime(rs.getInt("OccupyTime"));
            	meetroom.setAttenderNumber(rs.getInt("AttenderNumber"));
            	meetroom.setState(rs.getInt("State"));
            	meetroom.setDepartment(rs.getString("Department"));
            	meetroom.setRoomID(rs.getInt("RoomID"));
            	meetroom.setDescription(rs.getString("Description"));
            	meetroom.setReserverName(rs.getString("ReserverName"));
            	meetroom.setConnectorName(rs.getString("ConnectorName"));
            	meetroom.setConnectMeans(rs.getString("ConnectMeans"));
            	meetroom.setUserID(rs.getInt("UserId"));
            	meetingrooms.add(meetroom);
            }
			//close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
	}
		return meetingrooms;
}
	 //会议室分页 根据会议室查询
		public ArrayList<MeetingRoom> findRoomsByID(int RoomID,int pageCountNow){
			ArrayList<MeetingRoom> meetingrooms=new ArrayList<MeetingRoom>();
			int pageBefore=(pageCountNow-1)*10;
			String sql="SELECT * FROM campus_information.meetingroom WHERE RoomID = "+RoomID+" ORDER BY ReserveID DESC LIMIT "+pageBefore+",10";
			try {
				Connection con = DataBase.getConnection();
	            Statement stat=con.createStatement();
	            ResultSet rs=stat.executeQuery(sql);
	            while(rs.next())
	            {
	            	/*   ReserveID   BIGINT(20) NOT NULL AUTO_INCREMENT ,  
	   ReserveDate   varchar(40)  NOT NULL,
	   RoomID       int  NOT NULL,
	   OccupyTime   int  not null,
	   AttenderNumber int not null, 
	   ReserverName  varchar(30)  NOT NULL,
	   ConnectorName varchar(30)  NOT NULL,
	   ConnectMeans  varchar(30)  NOT NULL,
	   Description   varchar(300)  default '无',
	   Department    varchar(30)  not null,
	   State    int default 0,*/
	            	MeetingRoom meetroom=new MeetingRoom();
	            	meetroom.setReserveDate(rs.getString("ReserveDate"));
	            	meetroom.setReserveID(rs.getInt("ReserveID"));
	            	meetroom.setOccupyTime(rs.getInt("OccupyTime"));
	            	meetroom.setAttenderNumber(rs.getInt("AttenderNumber"));
	            	meetroom.setState(rs.getInt("State"));
	            	meetroom.setDepartment(rs.getString("Department"));
	            	meetroom.setRoomID(rs.getInt("RoomID"));
	            	meetroom.setDescription(rs.getString("Description"));
	            	meetroom.setReserverName(rs.getString("ReserverName"));
	            	meetroom.setConnectorName(rs.getString("ConnectorName"));
	            	meetroom.setConnectMeans(rs.getString("ConnectMeans"));
	            	meetroom.setUserID(rs.getInt("UserId"));
	            	meetingrooms.add(meetroom);
	            }
				//close work
				con.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
		}
			return meetingrooms;
	}
	//根据ReserveID返回会议室的信息
	public MeetingRoom findRoomsByReserveID(int ReserveID){
		MeetingRoom meetroom=new MeetingRoom();
		String sql="SELECT * FROM campus_information.meetingroom WHERE ReserveID="+ ReserveID+"";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	meetroom.setReserveDate(rs.getString("ReserveDate"));
            	meetroom.setReserveID(rs.getInt("ReserveID"));
            	meetroom.setOccupyTime(rs.getInt("OccupyTime"));
            	meetroom.setAttenderNumber(rs.getInt("AttenderNumber"));
            	meetroom.setState(rs.getInt("State"));
            	meetroom.setDepartment(rs.getString("Department"));
            	meetroom.setRoomID(rs.getInt("RoomID"));
            	meetroom.setDescription(rs.getString("Description"));
            	meetroom.setReserverName(rs.getString("ReserverName"));
            	meetroom.setConnectorName(rs.getString("ConnectorName"));
            	meetroom.setConnectMeans(rs.getString("ConnectMeans"));
            	meetroom.setUserID(rs.getInt("UserId"));
            }
			//close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
	}
		return meetroom;
}
	public int totalNumber(String choice, String input) {
		int total=0;
		//System.out.println("choice:"+choice);
       // System.out.println("input:"+input);
       // System.out.println("进入了total");
        
		String sql="";
		int RoomID=0;
		if(input.equals("")){
			sql = "SELECT COUNT(*) FROM campus_information.meetingroom";
		}else{
			if(choice.equals("时间")){
			sql = "SELECT COUNT(*) FROM campus_information.meetingroom WHERE ReserveDate LIKE '%"+input+"%'";	
			}else{
			if(input.equals("B2-617")){
				RoomID=1;
			}else if(input.equals("B2-708")){
				RoomID=2;
			}else if(input.equals("B2-810")){
				RoomID=3;
			}else if(input.equals("B2-811")){
				RoomID=4;
			};
			sql = "SELECT COUNT(*) FROM campus_information.meetingroom WHERE RoomID="+RoomID;
			};
			
		}
		try {
			Connection con = DataBase.getConnection();
			Statement stat = con.createStatement();
			ResultSet rs=stat.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			};
			// close work
			con.close();
		} catch (SQLException e2) {
			e2.printStackTrace();
		}
		return total;
	}
}
